package com.rocsea.robot.utils;

import com.rocsea.robot.exception.BusinessException;
import com.rocsea.robot.model.bo.ColumnInfo;
import com.rocsea.robot.exception.SqlRuntimeException;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * @Author RocSea
 * @Date 2022/12/2
 */
@SuppressWarnings("squid:S109")
public final class DatabaseUtils {

    private DatabaseUtils() {
        // hide constructor
    }

    /**
     * 获取列信息
     *
     * @param jdbcUrl      jdbc url
     * @param jdbcUsername jdbc 用户名
     * @param jdbcPassword jdbc 密码
     * @param schemaName   方案或数据库名
     * @param tableName    表名
     * @return 获取列信息
     */
    public static List<ColumnInfo> listColumnInfos(
            String jdbcUrl, String jdbcUsername, String jdbcPassword, String schemaName, String tableName) {
        final String sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
             PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setString(1, schemaName);
            statement.setString(2, tableName);
            final List<ColumnInfo> result = new ArrayList<>();
            try (final ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    final ColumnInfo columnInfo = new ColumnInfo();
                    final String columnName = resultSet.getString("COLUMN_NAME");
                    final String columnType = resultSet.getString("COLUMN_TYPE");
                    final String columnKey = resultSet.getString("COLUMN_KEY");
                    final String dataType = resultSet.getString("DATA_TYPE");
                    final String columnComment = resultSet.getString("COLUMN_COMMENT");
                    columnInfo.setColumnName(columnName);
                    columnInfo.setColumnType(columnType);
                    columnInfo.setColumnComment(columnComment);
                    columnInfo.setPk("PRI".equalsIgnoreCase(columnKey));
                    columnInfo.setDataType(dataType);
                    result.add(columnInfo);
                }
                return result;
            }
        } catch (Exception ex) {
            throw new SqlRuntimeException(ex);
        }
    }

    /**
     * 获取表数量
     *
     * @param jdbcUrl      jdbc url
     * @param jdbcUsername jdbc 用户名
     * @param jdbcPassword jdbc 密码
     * @param schemaName   方案或数据库名
     * @param tableName    表名
     * @return 获取表主键
     */
    public static Optional<Long> getTableCount(String jdbcUrl,
                                               String jdbcUsername,
                                               String jdbcPassword,
                                               String schemaName,
                                               String tableName,
                                               Map<String, Object> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(String.format("SELECT COUNT(0) AS COUNT FROM `%s`.`%s` WHERE 1=1", schemaName, tableName));
        if (!CollectionUtils.isEmpty(params)) {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                if (entry.getValue() instanceof Long || entry.getValue() instanceof Integer) {
                    sql.append(" and ").append(entry.getKey()).append(" = ").append(entry.getValue());
                } else if (entry.getValue() instanceof String) {
                    sql.append(" and ").append(entry.getKey()).append(" = '").append(entry.getValue()).append("'");
                } else {
                    throw new BusinessException("query param type not support ");
                }
            }
        }
        try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
             PreparedStatement statement = conn.prepareStatement(sql.toString())) {
            try (final ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    final long count = resultSet.getLong("COUNT");
                    return Optional.of(count);
                }
                return Optional.empty();
            }
        } catch (Exception ex) {
            throw new SqlRuntimeException(ex);
        }
    }

    /**
     * 获取创表语句
     *
     * @param jdbcUrl      jdbc url
     * @param jdbcUsername jdbc 用户名
     * @param jdbcPassword jdbc 密码
     * @return String
     */
    public static String getCreateTableSql(String jdbcUrl, String jdbcUsername, String jdbcPassword, String schemaName, String tableName) {
        final String sql = "SHOW CREATE TABLE " + schemaName + "." + tableName;
        try (Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
             PreparedStatement statement = conn.prepareStatement(sql)) {

            try (final ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    return resultSet.getString("Create Table");
                }
            }
            return null;
        } catch (Exception ex) {
            throw new SqlRuntimeException(ex);
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://192.168.9.151:3306?useSSL=false";
        String username = "root";
        String password = "DM#cgdev";
        String schemaName = "bond_ext_etl";
        String tableName = "abnormal_price";

        System.out.println(getCreateTableSql(url, username, password, schemaName, tableName));
    }

}
